﻿Imports System.Data.OleDb
Imports DTO

Public Class PhongDao
    Public Function TimKiem(ByVal pCrt As PhongCrt) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = BuildQuery(pCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt
    End Function

    Private Function BuildQuery(ByVal pCrt As PhongCrt, ByVal cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()
        Dim strDKMaPhong = " 1=1 "
        Dim strDKTenPhong = " 1=1 "
        Dim strDKLoaiPhong = " 1=1 "

        If (pCrt.MaPhong <> 0) Then
            strDKMaPhong = " MaPhong = ? "
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer)
            cmd.Parameters("@MaPhong").Value = pCrt.MaPhong
        End If
        'If (pCrt.Ten <> "") Then
        '    strDKTenHocSinh = " HoTen like ?"
        '    cmd.Parameters.Add("@HoTen", OleDbType.WChar)
        '    cmd.Parameters("@HoTen").Value = "%%" + svCrt.Ten + "%%"
        'End If
        'If (svCrt.CheckNgaySinh) Then
        '    strDKLoaiPhong = " NgaySinh between ? and ? "
        '    cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date)
        '    cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date)
        '    cmd.Parameters("@NgaySinhTu").Value = svCrt.NgaySinhTu
        '    cmd.Parameters("@NgaySinhDen").Value = svCrt.NgaySinhDen
        'End If
        'If (svCrt.CheckDiaChi And svCrt.DiaChi <> "") Then
        '    strDKDiaChi = " DiaChi like ? "
        '    cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        '    cmd.Parameters("@DiaChi").Value = "%" + svCrt.DiaChi + "%"
        'End If

        'If (svCrt.CheckDLT) Then
        '    strDKDiemLT = " DiemLyThuyet between ? and ? "
        '    cmd.Parameters.Add("@DiemLTTu", OleDbType.Double)
        '    cmd.Parameters.Add("@DiemLTDen", OleDbType.Double)
        '    cmd.Parameters("@DiemLTTu").Value = svCrt.DiemLTTu
        '    cmd.Parameters("@DiemLTDen").Value = svCrt.DiemLTDen
        'End If
        'If (svCrt.CheckDTH) Then
        '    strDKDiemTH = " DiemThucHanh between ? and ? "
        '    cmd.Parameters.Add("@DiemTHTu", OleDbType.Double)
        '    cmd.Parameters.Add("@DiemTHDen", OleDbType.Double)
        '    cmd.Parameters("@DiemTHTu").Value = svCrt.DiemTHTu
        '    cmd.Parameters("@DiemTHDen").Value = svCrt.DiemTHDen
        'End If
        'If (svCrt.CheckCMND And svCrt.CMND <> "") Then
        '    strDKCMND = " CMND like ? "
        '    cmd.Parameters.Add("@CMND", OleDbType.WChar)
        '    cmd.Parameters("@CMND").Value = "%" + svCrt.CMND + "%"
        'End If

        'If (svCrt.CheckGioiTinh) Then
        '    strDKGioiTinh = " GioiTinh like ? "
        '    cmd.Parameters.Add("@GioiTinh", OleDbType.WChar)
        '    cmd.Parameters("@GioiTinh").Value = "%" + svCrt.GioiTinh + "%"
        'End If
        If (pCrt.CheckTenPhong) Then
            strDKTenPhong = " TenPhong like ? "
            cmd.Parameters.Add("@TenPhong", OleDbType.WChar)
            cmd.Parameters("@TenPhong").Value = "%" + pCrt.TenPhong + "%"
        End If
        If (pCrt.CheckLoaiPhong) Then
            strDKLoaiPhong = " MaLoaiPhong = ? "
            cmd.Parameters.Add("@MaLoaiPhong", OleDbType.Integer)
            cmd.Parameters("@MaLoaiPhong").Value = pCrt.LoaiPhong
        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaPhong
        strDKWhere += " and " + strDKTenPhong
        strDKWhere += " and " + strDKLoaiPhong

        Dim strSQL As String = "Select * From DANHMUCPHONG "
        strSQL += strDKWhere
        strSQL += " Order by MaPhong "

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd
    End Function

    Public Function LayDSPhong() As List(Of PhongDto)
        Dim ds As New List(Of PhongDto)
        Dim cn As OleDbConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DataProvider.ConnectDB()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From DANHMUCPHONG"
        'B4: Thuc thi chuoi strSQL
        cn.Open()
        Dim cmd As New OleDbCommand(strSQL, cn)

        'Dim reader As OleDbDataReader = cmd.ExecuteReader()
        'Dim danhsach As New List(Of LoaiPhongDto)()
        'While reader.Read()
        '    Dim cnDTO As New LoaiPhongDto()
        '    cnDTO.MaLoaiPhong = reader.GetInt32(0)
        '    cnDTO.TenLoaiPhong = reader.GetString(1)
        '    cnDTO.DonGia = reader.GetDouble(2)
        '    danhsach.Add(cnDTO)

        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        Dim lp As New PhongDto()
        While (dr.Read())
            lp = New PhongDto()
            lp.MaPhong = dr("MaPhong")
            lp.TenPhong = dr("TenPhong")
            lp.MaLoaiPhong = dr("MaLoaiPhong")
            lp.TinhTrang = dr("TinhTrang")
            If dr("GhiChu").ToString.Length <> 0 Then
                lp.GhiChu = dr("GhiChu")
            End If


            ds.Add(lp)

        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return ds
    End Function

    Public Function ThemPhong(ByVal phongDto As PhongDto)
        Dim ketqua As Boolean = True
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Try
            conn.Open()
            Dim lenh As OleDbCommand
            Dim sql As String = "INSERT INTO DANHMUCPHONG (TenPhong,MaLoaiPhong,TinhTrang,GhiChu ) VALUES('" & phongDto.TenPhong & "'," & phongDto.MaLoaiPhong & ",'" & phongDto.TinhTrang & "','" & phongDto.GhiChu & "')"
            lenh = New OleDbCommand(sql, conn)
            Dim i As Integer = lenh.ExecuteNonQuery()
            If i <> 1 Then
                ketqua = False
            End If
        Catch ex As Exception
            ketqua = False
        Finally
            conn.Close()
        End Try
        Return ketqua

    End Function

End Class
